﻿<%@ WebService Language="C#" Class="DBProxy"  %>

using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.OleDb;
using System.Xml;
using System.Text;
using System.IO;
using System.Text.RegularExpressions;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// ASP.NET AJAX를 사용하여 스크립트에서 이 웹 서비스를 호출하려면 다음 줄의 주석 처리를 제거합니다. 
[System.Web.Script.Services.ScriptService]
public class DBProxy  : wsBase {

    /// http://www.codeproject.com/KB/webservices/Dynamic_Invoke_webservice.aspx
    /// 
    /// http://www.codeproject.com/KB/webservices/web-service-communicator.aspx
    /// http://www.codeproject.com/KB/java/Invoke_Java_WS_In_CS.aspx
    /// http://www.codeproject.com/KB/WCF/HelloWorldWCF.aspx
    
    
    /// <summary>
    /// xml인자를 받아서 지정된 프로시져를 호출하여 실행된 결과값을 전달받는다.
    /// <?xml version='1.0' encoding='utf-8' ?><DataTable><Record><@p1>aa</@p1><@p2>bb</@p2></Record></DataTable>
    /// </summary>
    /// <param name="cXml"></param>
    /// <returns></returns>
    [WebMethod(Description = "Svc01-&lt;DataTable&gt;&lt;Record&gt;&lt;@p1&gt;aa&lt;/@p1&gt;&lt;@p2&gt;bb&lt;/@p2&gt;&lt;/Record&gt;&lt;/DataTable&gt;")]
    public DataSet execProc(string SvcID, string XmlParms)
    {
    //&lt;?xml version='1.0' encoding='utf-8' ?&gt;&lt;DataTable&gt;&lt;Record&gt;&lt;p1&gt;aa&lt;/p1&gt;&lt;p2&gt;bb&lt;/p2&gt;&lt;/Record&gt;&lt;/DataTable&gt;
        
        DataSet df = new DataSet();
        XmlParms = XmlParms.Replace("&lt;", "<");
        XmlParms = XmlParms.Replace("&gt;", ">");
        XmlParms = XmlParms.Replace("&amp;", "&");
        //XmlParms = "<?xml version='1.0' encoding='utf-8' ?><DataTable><Record><p1>대한</p1><p2>민국</p2></Record></DataTable>";
        StringReader s = new StringReader("<?xml version='1.0' encoding='utf-8' ?>" + XmlParms);
        XmlTextReader xr = new XmlTextReader(s);
        df.ReadXml(xr);
                        
        
        DataSet ds = new DataSet();
        string filePath = System.AppDomain.CurrentDomain.BaseDirectory + @"\bzService\SvcXML\" + SvcID+".xml";
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.Load(filePath);
        XmlNode parent, root;
        parent = xmlDoc.SelectSingleNode("SVC");

        string ProcName = xmlDoc.SelectSingleNode("/SVC/CallName").InnerText;
        OleDbParameter[] arParms = DbObject.GetCachedParameters(ProcName);
        int pCnt = xmlDoc.SelectSingleNode("/SVC/Parameters").ChildNodes.Count;
        
        if (arParms == null)
        {
            
             arParms = new OleDbParameter[pCnt];
            for (int i = 0 ; i <pCnt; i++)
            {
                string pName = xmlDoc.SelectSingleNode("/SVC/Parameters").ChildNodes[i].Attributes["Name"].Value;
                OleDbType pType = (OleDbType)Enum.Parse(typeof(OleDbType), xmlDoc.SelectSingleNode("/SVC/Parameters").ChildNodes[i].Attributes["Type"].Value);
                int pLen = Convert.ToInt32(xmlDoc.SelectSingleNode("/SVC/Parameters").ChildNodes[i].Attributes["Len"].Value);
                arParms[i] = new OleDbParameter(pName, pType, pLen) ;
                
            }
            DbObject.CacheParameters(ProcName, arParms);
        }
       
        for (int i = 0; i < pCnt; i++)
        {
            string pName = xmlDoc.SelectSingleNode("/SVC/Parameters").ChildNodes[i].Attributes["Name"].Value;
            pName = pName.Replace("@", "");
            arParms[i].Value = df.Tables[0].Rows[0][pName];
        } 

        // SQL RUN
        ds = DbObject.ExecuteDataset(DbObject.CONN_STRING, CommandType.StoredProcedure, ProcName, arParms);
        return ds;
    }

    [WebMethod(Description = "Svc02-&lt;DataTable&gt;&lt;Record&gt;&lt;p1&gt;검색어&lt;/p1&gt;&lt;/Record&gt;&lt;/DataTable&gt;")]
    public string execComm(string SvcID, string XmlParms)
    {
         //HttpContext.Current.Request.Form["XmlParms"];
                
        //&lt;?xml version='1.0' encoding='utf-8' ?&gt;&lt;DataTable&gt;&lt;Record&gt;&lt;p1&gt;aa&lt;/p1&gt;&lt;p2&gt;bb&lt;/p2&gt;&lt;/Record&gt;&lt;/DataTable&gt;

        DataSet df = new DataSet();
        XmlParms = XmlParms.Replace("&lt;", "<");
        XmlParms = XmlParms.Replace("&gt;", ">");
        XmlParms = XmlParms.Replace("&amp;", "&");
        //XmlParms = "<?xml version='1.0' encoding='utf-8' ?><DataTable><Record><p1>성민</p1></Record></DataTable>";
        StringReader s = new StringReader("<?xml version='1.0' encoding='utf-8' ?>"+XmlParms);
        XmlTextReader xr = new XmlTextReader(s);
        df.ReadXml(xr);


        // 서비스의 해당 SQL을 추출파일에서 읽어온다
        DataSet ds = new DataSet();
        string filePath = System.AppDomain.CurrentDomain.BaseDirectory + @"\bzService\SvcXML\" + SvcID + ".xml";
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.Load(filePath);
        XmlNode parent;
        parent = xmlDoc.SelectSingleNode("SVC");
         
        int pCnt = xmlDoc.SelectSingleNode("/SVC/Parameters").ChildNodes.Count;
        object[] arParms = new object[pCnt];
    
        for (int i = 0; i < pCnt; i++)
        {
            string pName = xmlDoc.SelectSingleNode("/SVC/Parameters").ChildNodes[i].Attributes["Name"].Value;
            pName = pName.Replace("@", "");
            if (df.Tables[0].Columns.IndexOf(pName) > -1)
                arParms[i] = df.Tables[0].Rows[0][pName];
            else
                arParms[i] = "";
            
        }

        string SqlComm = string.Format(xmlDoc.SelectSingleNode("/SVC/Sql").InnerText,arParms)  ;
        
        // SQL RUN
        ds = DbObject.ExecuteDataset(DbObject.CONN_STRING, CommandType.Text, SqlComm);
        string Json = GetJSONString(ds.Tables[0], ds.Tables[0].Rows.Count.ToString(), "1", "1");
        return Json;
    }


    [WebMethod(Description = "Svc02-&lt;DataTable&gt;&lt;Record&gt;&lt;p1&gt;검색어&lt;/p1&gt;&lt;/Record&gt;&lt;/DataTable&gt;")]
    public string execRun(string SvcID, string XmlParms)
    {
        //&lt;?xml version='1.0' encoding='utf-8' ?&gt;&lt;DataTable&gt;&lt;Record&gt;&lt;p1&gt;aa&lt;/p1&gt;&lt;p2&gt;bb&lt;/p2&gt;&lt;/Record&gt;&lt;/DataTable&gt;

        DataSet df = new DataSet();
        XmlParms = XmlParms.Replace("&lt;", "<");
        XmlParms = XmlParms.Replace("&gt;", ">");
        XmlParms = XmlParms.Replace("&amp;", "&");
        //XmlParms = "<?xml version='1.0' encoding='utf-8' ?><DataTable><Record><p1>성민</p1></Record></DataTable>";
        StringReader s = new StringReader("<?xml version='1.0' encoding='utf-8' ?>" + XmlParms);
        XmlTextReader xr = new XmlTextReader(s);
        df.ReadXml(xr);


        // 서비스의 해당 SQL을 추출파일에서 읽어온다
        DataSet ds = new DataSet();
        string filePath = System.AppDomain.CurrentDomain.BaseDirectory + @"\bzService\SvcXML\" + SvcID + ".xml";
        XmlDocument xmlDoc = new XmlDocument();
        xmlDoc.Load(filePath);
        XmlNode parent;
        parent = xmlDoc.SelectSingleNode("SVC");

        int pCnt = xmlDoc.SelectSingleNode("/SVC/Parameters").ChildNodes.Count;
        object[] arParms = new object[pCnt];

        for (int i = 0; i < pCnt; i++)
        {
            string pName = xmlDoc.SelectSingleNode("/SVC/Parameters").ChildNodes[i].Attributes["Name"].Value;
            pName = pName.Replace("@", "");
            if (df.Tables[0].Columns.IndexOf(pName) > -1)
                arParms[i] = df.Tables[0].Rows[0][pName];
            else
                arParms[i] = "";

        }

        string SqlComm = string.Format(xmlDoc.SelectSingleNode("/SVC/Sql").InnerText, arParms);

        // SQL RUN
        int rlt = DbObject.ExecuteNonQuery(DbObject.CONN_STRING, CommandType.Text, SqlComm);

        return rlt.ToString();
    }


    [WebMethod]
    public string Getpara()
    {
        string Sql = string.Format(@"

	    SELECT  
        '<LI><A Q1=''' + CONVERT(VARCHAR(10),A.CSTID) + ''' CLASS=''liContactList''>'+
        '<H3>' + A.CSTNAME + '</H3>'+
        '<P><STRONG>' + ISNULL(A.DEPARTMENT,'') + ' / ' + ISNULL(A.JOBTITLE,'') + '</STRONG></P>'+
        '<P>' + ISNULL(A.WRKTEL,'') + ' / ' + ISNULL(A.MOBILE,'') + ' / ' + ISNULL(A.EMAIL,'') + ' </P>'+
        '<P>' + ISNULL(WRKUPRADDRESS+' '+WRKLWRADDRESS,'') + '</P>'+
        '<p class=''ui-li-aside''><strong>' + ISNULL(B.ROLEDESC,'') + '</strong></p>'+                
        '</A></LI>'  AS LI_ITEM, A.CSTID AS LI_KEY
        FROM TB280010 B INNER JOIN TB100010 A ON A.CSTID = B.CSTID
        WHERE A.SITEID = 102
        and B.CLIID = '@p1' and B.Cstname = @cse

");
            
        Regex reg = new Regex("@\\w+", RegexOptions.Multiline);

        MatchCollection matchCol = reg.Matches(Sql);
        
        
        
        
        return "";
        
    }
}